﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;



using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.MarketingPaymentDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class MarketingPaymentBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.MarketingPayment; } }

        private UserInformation userOrganization;

        private MarketingPaymentMaster marketingPaymentMaster;

        private MyBindingList<MarketingPaymentDetail> marketingPaymentDetailList;

        private bool ReadonlyMode { get; set; }

        public MarketingPaymentBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                marketingPaymentMaster = new MarketingPaymentMaster();

                this.marketingPaymentDetailList = new MyBindingList<MarketingPaymentDetail>();



                this.arrayFilterDescription = new string[1, 2];



                GlobalDefaultValue.Apply(this);


                this.MarketingPaymentMaster.PropertyChanged += new PropertyChangedEventHandler(MarketingPaymentMaster_PropertyChanged);
                this.marketingPaymentDetailList.ListChanged += new ListChangedEventHandler(MarketingPaymentDetail_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void MarketingPaymentMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "MarketingProgramID" || e.PropertyName == "MarketingIncentiveID" || e.PropertyName == "MarketingPaymentTermID" || e.PropertyName == "CustomerID"))
            {
                this.StopTracking();
                this.MarketingPaymentGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void MarketingPaymentDetail_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private MarketingPaymentListingTableAdapter listingTableAdapter;
        protected MarketingPaymentListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new MarketingPaymentListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private MarketingPaymentMasterTableAdapter masterTableAdapter;
        protected MarketingPaymentMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new MarketingPaymentMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        MarketingPaymentDetailTableAdapter marketingPaymentDetailTableAdapter;
        protected MarketingPaymentDetailTableAdapter MarketingPaymentDetailTableAdapter
        {
            get
            {
                if (marketingPaymentDetailTableAdapter == null) marketingPaymentDetailTableAdapter = new MarketingPaymentDetailTableAdapter();
                return marketingPaymentDetailTableAdapter;
            }
        }


        MarketingPaymentGetDetailTableAdapter marketingPaymentGetDetailTableAdapter;
        protected MarketingPaymentGetDetailTableAdapter MarketingPaymentGetDetailTableAdapter
        {
            get
            {
                if (marketingPaymentGetDetailTableAdapter == null) marketingPaymentGetDetailTableAdapter = new MarketingPaymentGetDetailTableAdapter();
                return marketingPaymentGetDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public MarketingPaymentDTS.MarketingPaymentListingDataTable MarketingPaymentMasterList(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public MarketingPaymentMaster MarketingPaymentMaster
        {
            get { return this.marketingPaymentMaster; }
        }

        public MyBindingList<MarketingPaymentDetail> MarketingPaymentDetailList
        {
            get { return this.marketingPaymentDetailList; }
        }




        private string[,] arrayFilterDescription;

        /// <summary>
        /// This get user friendly filter description from the arrayFilterDescription, BUT the arrayFilterDescription is only updated by this.GetFilterMarketingProgramID()
        /// So: To use this FilterDescription, please ONLY TAKE IT AFTER SOME METHED HAS CALLED This.GetFilterMarketingProgramID()
        /// </summary>
        /// <returns></returns>
        public string[,] FilterDescription()
        {
            return arrayFilterDescription;
        }

        public DataTable GetMarketingPaymentListingDetail()
        {
            arrayFilterDescription[0, 0] = "Date"; arrayFilterDescription[0, 1] = GlobalVariables.GlobalOptionSetting.LowerFillterDate.ToString("dd/MM/yyyy") + " -> " + GlobalVariables.GlobalOptionSetting.UpperFillterDate.ToString("dd/MM/yyyy");


            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@LowerFillterDate", GlobalVariables.GlobalOptionSetting.LowerFillterDate); sqlParameter[0].SqlDbType = SqlDbType.DateTime; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@UpperFillterDate", GlobalVariables.GlobalOptionSetting.UpperFillterDate); sqlParameter[1].SqlDbType = SqlDbType.DateTime; sqlParameter[1].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingPaymentListingDetail", CommandType.StoredProcedure, sqlParameter);
        }

        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int MarketingPaymentID   //Primary Key
        {
            get { return this.MarketingPaymentMaster.MarketingPaymentID; }
            private set
            {
                if (this.MarketingPaymentMaster.MarketingPaymentID != value)
                {
                    this.StopTracking();

                    this.MarketingPaymentMaster.MarketingPaymentID = value;

                    this.MarketingPaymentGetMaster();
                    this.MarketingPaymentGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        
        #endregion <Primary Key>

        private void MarketingPaymentGetMaster()
        {
            if (this.MarketingPaymentID > 0)
            {
                MarketingPaymentDTS.MarketingPaymentMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.MarketingPaymentID);

                if (masterDataTable.Count > 0)
                {
                    this.MarketingPaymentMaster.StopTracking();

                    this.MarketingPaymentMaster.CustomerID = masterDataTable[0].CustomerID;
                    this.MarketingPaymentMaster.CustomerDescription = masterDataTable[0].CustomerDescription;
                    this.MarketingPaymentMaster.MarketingProgramID = masterDataTable[0].MarketingProgramID;
                    this.MarketingPaymentMaster.MarketingProgramReference = masterDataTable[0].MarketingProgramReference;
                    this.MarketingPaymentMaster.MarketingIncentiveID = masterDataTable[0].MarketingIncentiveID;
                    this.MarketingPaymentMaster.MarketingIncentivePeriod = masterDataTable[0].MarketingIncentivePeriod;
                    this.MarketingPaymentMaster.MarketingPaymentTermID = masterDataTable[0].MarketingPaymentTermID;
                    this.MarketingPaymentMaster.MarketingPaymentTermDescription = masterDataTable[0].MarketingPaymentTermDescription;

                    this.MarketingPaymentMaster.PaymentDate = masterDataTable[0].PaymentDate;
                    this.MarketingPaymentMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.MarketingPaymentMaster.ReferenceCode = masterDataTable[0].ReferenceCode;
                    this.MarketingPaymentMaster.PaymentMachanics = masterDataTable[0].PaymentMachanics;

                    this.MarketingPaymentMaster.RequestedEmployeeID = masterDataTable[0].RequestedEmployeeID;
                    this.MarketingPaymentMaster.NotedEmployeeID = masterDataTable[0].NotedEmployeeID;
                    this.MarketingPaymentMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;

                    this.MarketingPaymentMaster.Remarks = masterDataTable[0].Remarks;

                    this.MarketingPaymentMaster.StartTracking();

                    this.MarketingPaymentMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.MarketingPaymentMaster);
                this.MarketingPaymentMaster.PaymentDate = DateTime.Today;
                this.MarketingPaymentMaster.EntryDate = DateTime.Today;
                this.MarketingPaymentMaster.Reset();
            }
        }


        private void MarketingPaymentGetDetail()
        {
            this.marketingPaymentDetailList.RaiseListChangedEvents = false;
            this.marketingPaymentDetailList.Clear();
            if (this.MarketingPaymentID >= 0)
            {
                if (this.MarketingPaymentMaster.MarketingProgramID > 0 && this.MarketingPaymentMaster.MarketingIncentiveID >= 0 && this.MarketingPaymentMaster.MarketingPaymentTermID >= 0 && this.MarketingPaymentMaster.CustomerID >= 0)//Fix this, because it run too slow
                {
                    MarketingPaymentDTS.MarketingPaymentGetDetailDataTable marketingPaymentGetDetailDataTable = this.MarketingPaymentGetDetailTableAdapter.GetData(this.MarketingPaymentID, this.MarketingPaymentMaster.MarketingProgramID, this.MarketingPaymentMaster.MarketingIncentiveID, this.MarketingPaymentMaster.MarketingPaymentTermID, this.MarketingPaymentMaster.CustomerID, this.ReadonlyMode);

                    if (marketingPaymentGetDetailDataTable.Rows.Count > 0)
                    {
                        int marketingIncentiveID = 0; int marketingIncentiveSerialID = 0;
                        foreach (MarketingPaymentDTS.MarketingPaymentGetDetailRow marketingPaymentGetDetailRow in marketingPaymentGetDetailDataTable.Rows)
                        {
                            if (marketingIncentiveID != marketingPaymentGetDetailRow.MarketingIncentiveID || marketingIncentiveSerialID != marketingPaymentGetDetailRow.MarketingIncentiveSerialID)
                            {
                                marketingIncentiveID = marketingPaymentGetDetailRow.MarketingIncentiveID; marketingIncentiveSerialID = marketingPaymentGetDetailRow.MarketingIncentiveSerialID;
                                this.marketingPaymentDetailList.Add(new MarketingPaymentDetail(marketingPaymentGetDetailRow.MarketingIncentiveID, marketingPaymentGetDetailRow.MarketingIncentiveSerialID, marketingPaymentGetDetailRow.RequestedDate, marketingPaymentGetDetailRow.PaymentPeriod, marketingPaymentGetDetailRow.CustomerID, marketingPaymentGetDetailRow.CustomerCode, marketingPaymentGetDetailRow.CustomerName, marketingPaymentGetDetailRow.MarketingPaymentTermID, marketingPaymentGetDetailRow.TurnOverAmount, marketingPaymentGetDetailRow.IncentiveAmount, marketingPaymentGetDetailRow.RemainsAmount, marketingPaymentGetDetailRow.PaymentAmount, marketingPaymentGetDetailRow.Description, marketingPaymentGetDetailRow.Remarks));
                            }
                        }
                    }
                }
            }

            this.marketingPaymentDetailList.RaiseListChangedEvents = true;
            this.marketingPaymentDetailList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<MarketingPaymentDetail> inValidMarketingPaymentDetail = this.MarketingPaymentDetailList.Where(marketingPaymentDetail => !marketingPaymentDetail.IsValid).ToList();
                return this.MarketingPaymentMaster.IsValid && inValidMarketingPaymentDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.marketingPaymentMaster.PaymentDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("SPMarketingPaymentApproved", this.MarketingPaymentID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingPaymentEditable", this.MarketingPaymentID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.marketingPaymentMaster.PaymentDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.MarketingPaymentVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingPaymentEditable", this.MarketingPaymentID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.marketingPaymentMaster.PaymentDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.MarketingPaymentUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingPaymentEditable", this.MarketingPaymentID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int marketingPaymentID)
        {
            this.ReadonlyMode = true;
            if (this.MarketingPaymentID == marketingPaymentID) this.MarketingPaymentID = -1; //Enforce to reload
            this.MarketingPaymentID = marketingPaymentID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.MarketingPaymentID == 0) this.MarketingPaymentID = -1;
            this.MarketingPaymentID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int marketingPaymentID = this.MarketingPaymentID;

            this.MarketingPaymentID = -1;
            this.MarketingPaymentID = marketingPaymentID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int marketingPaymentID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref marketingPaymentID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(marketingPaymentID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }

                this.Fill(marketingPaymentID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.marketingPaymentMaster.MarketingPaymentID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.marketingPaymentMaster.MarketingPaymentID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.marketingPaymentMaster.MarketingPaymentID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.MarketingPaymentMaster.RequestedEmployeeID, this.MarketingPaymentMaster.PaymentDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int marketingPaymentID)
        {
            MarketingPaymentDTS.MarketingPaymentMasterDataTable masterDataTable;
            MarketingPaymentDTS.MarketingPaymentMasterRow masterRow;

            if (this.marketingPaymentMaster.MarketingPaymentID <= 0) //Add
            {
                masterDataTable = new MarketingPaymentDTS.MarketingPaymentMasterDataTable();
                masterRow = masterDataTable.NewMarketingPaymentMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(marketingPaymentMaster.MarketingPaymentID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(marketingPaymentMaster.MarketingPaymentID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.CustomerID = this.marketingPaymentMaster.CustomerID;
            masterRow.MarketingProgramID = this.marketingPaymentMaster.MarketingProgramID;
            masterRow.MarketingIncentiveID = this.marketingPaymentMaster.MarketingIncentiveID;
            masterRow.MarketingPaymentTermID = this.marketingPaymentMaster.MarketingPaymentTermID;

            masterRow.PaymentDate = this.marketingPaymentMaster.PaymentDate;
            masterRow.EntryDate = this.marketingPaymentMaster.EntryDate;

            masterRow.ReferenceCode = this.marketingPaymentMaster.ReferenceCode;
            masterRow.PaymentMachanics = this.marketingPaymentMaster.PaymentMachanics;

            masterRow.RequestedEmployeeID = this.marketingPaymentMaster.RequestedEmployeeID;
            masterRow.NotedEmployeeID = this.marketingPaymentMaster.NotedEmployeeID;
            masterRow.ApprovedEmployeeID = this.marketingPaymentMaster.ApprovedEmployeeID;

            masterRow.Remarks = this.marketingPaymentMaster.Remarks;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.marketingPaymentMaster.MarketingPaymentID <= 0) masterDataTable.AddMarketingPaymentMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            marketingPaymentID = masterRow.MarketingPaymentID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int marketingPaymentID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsPayment = 0;

            MarketingPaymentDTS.MarketingPaymentDetailDataTable marketingPaymentDetailDataTable = new MarketingPaymentDTS.MarketingPaymentDetailDataTable();

            foreach (MarketingPaymentDetail marketingPaymentDetail in this.marketingPaymentDetailList)
            {
                if (marketingPaymentDetail.PaymentAmount > 0)
                {
                    rowsPayment++;

                    MarketingPaymentDTS.MarketingPaymentDetailRow marketingPaymentDetailRow = marketingPaymentDetailDataTable.NewMarketingPaymentDetailRow();

                    marketingPaymentDetailRow.MarketingPaymentID = marketingPaymentID;
                    marketingPaymentDetailRow.SerialID = ++serialID;

                    marketingPaymentDetailRow.MarketingIncentiveID = marketingPaymentDetail.MarketingIncentiveID;
                    marketingPaymentDetailRow.MarketingIncentiveSerialID = marketingPaymentDetail.MarketingIncentiveSerialID;

                    marketingPaymentDetailRow.CustomerID = marketingPaymentDetail.CustomerID;
                    marketingPaymentDetailRow.MarketingProgramID = this.MarketingPaymentMaster.MarketingProgramID;

                    marketingPaymentDetailRow.MarketingPaymentTermID = marketingPaymentDetail.MarketingPaymentTermID;

                    marketingPaymentDetailRow.PaymentAmount = marketingPaymentDetail.PaymentAmount;

                    marketingPaymentDetailRow.Description = marketingPaymentDetail.Description;
                    marketingPaymentDetailRow.Remarks = marketingPaymentDetail.Remarks;

                    marketingPaymentDetailDataTable.AddMarketingPaymentDetailRow(marketingPaymentDetailRow);
                }
            }

            rowsAffected = this.MarketingPaymentDetailTableAdapter.Update(marketingPaymentDetailDataTable);
            if (rowsAffected != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save detail");


            int rowsUpdated = this.MarketingPaymentDetailTableAdapter.MarketingPaymentSaveUpdate(marketingPaymentID, (int)GlobalMsADO.UpdateOption.SaveUpdate);
            if (rowsUpdated != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save update");


        #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int marketingPaymentID)
        {
            int rowsUpdated = this.MarketingPaymentDetailTableAdapter.MarketingPaymentSaveUpdate(marketingPaymentID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.MarketingPaymentDetailTableAdapter.Delete(marketingPaymentID);

            if (rowsUpdated != rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo");

            return true;
        }


        private bool SaveConflict()
        {
            return GlobalDataObjectAction.SaveConflict("MarketingPaymentSaveConflict", this.MarketingPaymentID);
        }



        #region Import Excel

        public bool ImportMarketingPayment(string fileName)
        {
            try
            {
                if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict"); // || !this.IsDirty

                double paymentAmount = 0;
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.MarketingPayment, fileName);
                if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                {
                    //Step 1: Check all rows to validate imported data
                    foreach (DataRow excelDataRow in excelDataTable.Rows)
                    {
                        if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                        {
                            if (paymentAmount > 0)
                            {
                                MarketingPaymentDetail marketingPaymentDetail = this.MarketingPaymentDetailList.FindItem(TypeDescriptor.CreateProperty(typeof(MarketingPaymentDetail), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                if (marketingPaymentDetail == null || marketingPaymentDetail.RemainsAmount < Math.Round(marketingPaymentDetail.PaymentAmount + paymentAmount, GlobalVariables.Round0Quantity))
                                    exceptionTable.AddException(new string[] { marketingPaymentDetail == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                            }
                        }
                        else
                            exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                    }

                    //Step 2: Start import
                    if (exceptionTable.Table.Rows.Count <= 0)
                    {
                        this.MarketingPaymentDetailList.RaiseListChangedEvents = false;

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                            {
                                if (paymentAmount > 0)
                                {
                                    MarketingPaymentDetail marketingPaymentDetail = this.MarketingPaymentDetailList.FindItem(TypeDescriptor.CreateProperty(typeof(MarketingPaymentDetail), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                    if (marketingPaymentDetail == null || marketingPaymentDetail.RemainsAmount < Math.Round(marketingPaymentDetail.PaymentAmount + paymentAmount, GlobalVariables.Round0Quantity))
                                        exceptionTable.AddException(new string[] { marketingPaymentDetail == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                                    else
                                    {
                                        marketingPaymentDetail.PaymentAmount = marketingPaymentDetail.PaymentAmount + paymentAmount;
                                        marketingPaymentDetail.Remarks = excelDataRow["Remarks"].ToString();
                                    }
                                }
                            }
                            else
                                exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                        }

                        this.MarketingPaymentDetailList.RaiseListChangedEvents = true;
                        this.MarketingPaymentDetailList.ResetBindings();
                    }

                    if (exceptionTable.Table.Rows.Count > 0)
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);
                }
                return true;
            }
            catch (System.Exception exception)
            {
                this.MarketingPaymentDetailList.RaiseListChangedEvents = true;
                throw exception;
            }
        }

        #endregion Import Excel



        private void RestoreProcedure()
        {

            SQLDatabase.ExecuteNonQuery("UPDATE MarketingIncentiveDetail SET TurnOverAmount =  ROUND(TurnOverAmount,0),  IncentiveAmount =  ROUND(IncentiveAmount,0), PaidAmount = ROUND(PaidAmount,0)");


            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          LEFT(CONVERT(char(10), MarketingPaymentMaster.PaymentDate, 111), 7) AS BeginingMonth, MarketingPaymentMaster.MarketingPaymentID, MarketingPaymentMaster.PaymentDate, MarketingPaymentMaster.ReferenceCode, MarketingProgramMaster.Reference AS ProgramReference, MarketingProgramMaster.ReferenceCode AS ProgramReferenceCode, ISNULL(MarketingProgramMaster.MarketingProgramName, '') AS MarketingProgramName, ISNULL(MarketingProgramMaster.MarketingProgramSubName, '') AS MarketingProgramSubName, MarketingIncentiveMaster.RequestedDate, ISNULL(MarketingIncentiveMaster.PaymentPeriod, '') AS PaymentPeriod " + "\r\n";
            queryString = queryString + "       FROM            MarketingPaymentMaster LEFT JOIN " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster ON MarketingPaymentMaster.MarketingProgramID = MarketingProgramMaster.MarketingProgramID LEFT JOIN " + "\r\n";
            queryString = queryString + "                       MarketingIncentiveMaster ON MarketingPaymentMaster.MarketingIncentiveID = MarketingIncentiveMaster.MarketingIncentiveID " + "\r\n";
            queryString = queryString + "       WHERE           MarketingPaymentMaster.PaymentDate >= @LowerFillterDate AND MarketingPaymentMaster.PaymentDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentListing", queryString);


            queryString = " @MarketingPaymentID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          MarketingPaymentID, CustomerID, ISNULL((SELECT Description FROM ListCustomerName WHERE ListCustomerName.CustomerID = MarketingPaymentMaster.CustomerID), '') AS CustomerDescription, " + "\r\n";
            queryString = queryString + "                       MarketingProgramID, (SELECT Reference FROM MarketingProgramMaster WHERE MarketingPaymentMaster.MarketingProgramID = MarketingProgramMaster.MarketingProgramID) AS MarketingProgramReference, " + "\r\n";
            queryString = queryString + "                       MarketingIncentiveID, ISNULL((SELECT PaymentPeriod FROM MarketingIncentiveMaster WHERE MarketingIncentiveMaster.MarketingIncentiveID = MarketingPaymentMaster.MarketingIncentiveID), '') AS MarketingIncentivePeriod, " + "\r\n";
            queryString = queryString + "                       MarketingPaymentTermID, ISNULL((SELECT Description FROM ListMarketingPaymentTerm WHERE ListMarketingPaymentTerm.MarketingPaymentTermID = MarketingPaymentMaster.MarketingPaymentTermID), '') AS MarketingPaymentTermDescription, " + "\r\n";
            queryString = queryString + "                       PaymentDate, EntryDate, ReferenceCode, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved " + "\r\n";

            queryString = queryString + "       FROM            MarketingPaymentMaster " + "\r\n";
            queryString = queryString + "       WHERE           MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentMasterSelect", queryString);


            queryString = " @CustomerID int, @MarketingProgramID int, @MarketingIncentiveID int, @MarketingPaymentTermID int,  @PaymentDate datetime, @EntryDate datetime, @ReferenceCode nvarchar(100), @PaymentMachanics nvarchar(1000), @RequestedEmployeeID int, @NotedEmployeeID int, @ApprovedEmployeeID int, @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     MarketingPaymentMaster (CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingPaymentTermID, PaymentDate, EntryDate, ReferenceCode, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved) VALUES (@CustomerID, @MarketingProgramID, @MarketingIncentiveID, @MarketingPaymentTermID, @PaymentDate, @EntryDate, @ReferenceCode, @PaymentMachanics, @RequestedEmployeeID, @NotedEmployeeID, @ApprovedEmployeeID, @Remarks, @UserID, @UserOrganizationID, @Approved) " + "\r\n";
            queryString = queryString + "       SELECT MarketingPaymentID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingPaymentTermID, PaymentDate, EntryDate, ReferenceCode, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved FROM MarketingPaymentMaster WHERE MarketingPaymentID = SCOPE_IDENTITY() " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentMasterInsert", queryString);


            queryString = " @MarketingPaymentID int, @CustomerID int, @MarketingProgramID int, @MarketingIncentiveID int, @MarketingPaymentTermID int, @PaymentDate datetime, @EntryDate datetime, @ReferenceCode nvarchar(100), @PaymentMachanics nvarchar(1000), @RequestedEmployeeID int, @NotedEmployeeID int, @ApprovedEmployeeID int, @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          MarketingPaymentMaster SET CustomerID = @CustomerID, MarketingProgramID = @MarketingProgramID, MarketingIncentiveID = @MarketingIncentiveID, MarketingPaymentTermID = @MarketingPaymentTermID, PaymentDate = @PaymentDate, EntryDate = @EntryDate, ReferenceCode = @ReferenceCode, PaymentMachanics = @PaymentMachanics, RequestedEmployeeID = @RequestedEmployeeID, NotedEmployeeID = @NotedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved WHERE MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            queryString = queryString + "       SELECT          MarketingPaymentID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingPaymentTermID, PaymentDate, EntryDate, ReferenceCode, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, Remarks, UserID, UserOrganizationID, Approved FROM MarketingPaymentMaster WHERE MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentMasterUpdate", queryString);


            queryString = " @MarketingPaymentID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     MarketingPaymentMaster WHERE MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentMasterDelete", queryString);


            queryString = " @MarketingPaymentID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          MarketingPaymentID, SerialID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingIncentiveSerialID, MarketingPaymentTermID, PaymentAmount, Description, Remarks " + "\r\n";
            queryString = queryString + "       FROM            MarketingPaymentDetail " + "\r\n";
            queryString = queryString + "       WHERE           MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentDetailSelect", queryString);


            queryString = " @MarketingPaymentID int, @SerialID int,	@CustomerID int, @MarketingProgramID int, @MarketingIncentiveID int, @MarketingIncentiveSerialID int, @MarketingPaymentTermID int, @PaymentAmount float, @Description nvarchar(100), @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     MarketingPaymentDetail (MarketingPaymentID, SerialID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingIncentiveSerialID, MarketingPaymentTermID, PaymentAmount, Description, Remarks) VALUES (@MarketingPaymentID, @SerialID, @CustomerID, @MarketingProgramID, @MarketingIncentiveID, @MarketingIncentiveSerialID, @MarketingPaymentTermID, @PaymentAmount, @Description, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT          MarketingPaymentID, SerialID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingIncentiveSerialID, MarketingPaymentTermID, PaymentAmount, Description, Remarks FROM MarketingPaymentDetail WHERE (MarketingPaymentID = @MarketingPaymentID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentDetailInsert", queryString);


            queryString = " @MarketingPaymentID int, @SerialID int,	@CustomerID int, @MarketingProgramID int, @MarketingIncentiveID int, @MarketingIncentiveSerialID int, @MarketingPaymentTermID int, @PaymentAmount float, @Description nvarchar(100), @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          MarketingPaymentDetail SET CustomerID = @CustomerID, MarketingProgramID = @MarketingProgramID, MarketingIncentiveID = @MarketingIncentiveID, MarketingIncentiveSerialID = @MarketingIncentiveSerialID, MarketingPaymentTermID = @MarketingPaymentTermID, PaymentAmount = @PaymentAmount, Description = @Description, Remarks = @Remarks WHERE MarketingPaymentID = @MarketingPaymentID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT          MarketingPaymentID, SerialID, CustomerID, MarketingProgramID, MarketingIncentiveID, MarketingIncentiveSerialID, MarketingPaymentTermID, PaymentAmount, Description, Remarks FROM MarketingPaymentDetail WHERE MarketingPaymentID = @MarketingPaymentID AND SerialID = @SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentDetailUpdate", queryString);


            queryString = " @MarketingPaymentID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE          FROM     MarketingPaymentDetail WHERE MarketingPaymentID = @MarketingPaymentID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentDetailDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          MarketingIncentiveDetail.MarketingIncentiveID AS MarketingIncentiveID, MarketingIncentiveDetail.SerialID AS MarketingIncentiveSerialID, MarketingIncentiveMaster.RequestedDate, MarketingIncentiveMaster.PaymentPeriod, ListCustomerName.CustomerID, ListCustomerName.Description AS CustomerCode, ListCustomerName.DescriptionOfficial AS CustomerName, MarketingPaymentDetail.MarketingPaymentTermID, " + "\r\n";
            queryEdit = queryEdit + "                   MarketingIncentiveDetail.TurnOverAmount, MarketingIncentiveDetail.IncentiveAmount, ROUND(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount + MarketingPaymentDetail.PaymentAmount, 0) AS RemainsAmount, MarketingPaymentDetail.PaymentAmount, MarketingPaymentDetail.Description, MarketingPaymentDetail.Remarks, 0 AS RSC " + "\r\n";
            queryEdit = queryEdit + "   FROM            MarketingIncentiveMaster INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   MarketingIncentiveDetail ON MarketingIncentiveMaster.MarketingIncentiveID = MarketingIncentiveDetail.MarketingIncentiveID INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   MarketingPaymentDetail ON MarketingIncentiveDetail.MarketingIncentiveID = MarketingPaymentDetail.MarketingIncentiveID AND MarketingIncentiveDetail.SerialID = MarketingPaymentDetail.MarketingIncentiveSerialID INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   ListCustomerName ON MarketingIncentiveDetail.CustomerID = ListCustomerName.CustomerID INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   ListMarketingPaymentTerm ON MarketingIncentiveDetail.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@MarketingProgramID = -1 OR @MarketingProgramID = 0 OR MarketingIncentiveDetail.MarketingProgramID = @MarketingProgramID) AND (@MarketingIncentiveID = -1 OR @MarketingIncentiveID = 0 OR MarketingIncentiveDetail.MarketingIncentiveID = @MarketingIncentiveID) AND (@MarketingPaymentTermID = -1 OR @MarketingPaymentTermID = 0 OR MarketingIncentiveDetail.MarketingPaymentTermID = @MarketingPaymentTermID) AND (@CustomerID = 0 OR MarketingIncentiveDetail.CustomerID = @CustomerID) AND MarketingPaymentDetail.MarketingPaymentID = @MarketingPaymentID " + "\r\n";

            queryNew = "                SELECT          MarketingIncentiveDetail.MarketingIncentiveID AS MarketingIncentiveID, MarketingIncentiveDetail.SerialID AS MarketingIncentiveSerialID, MarketingIncentiveMaster.RequestedDate, MarketingIncentiveMaster.PaymentPeriod, ListCustomerName.CustomerID, ListCustomerName.Description AS CustomerCode, ListCustomerName.DescriptionOfficial AS CustomerName, MarketingIncentiveDetail.MarketingPaymentTermID, " + "\r\n";
            queryNew = queryNew + "                     MarketingIncentiveDetail.TurnOverAmount, MarketingIncentiveDetail.IncentiveAmount, ROUND(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount, 0) AS RemainsAmount, 0.0 AS PaymentAmount, MarketingIncentiveDetail.Description, MarketingIncentiveDetail.Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            MarketingIncentiveMaster INNER JOIN " + "\r\n";
            queryNew = queryNew + "                     MarketingIncentiveDetail ON MarketingIncentiveMaster.MarketingIncentiveID = MarketingIncentiveDetail.MarketingIncentiveID INNER JOIN " + "\r\n";
            queryNew = queryNew + "                     ListCustomerName ON MarketingIncentiveDetail.CustomerID = ListCustomerName.CustomerID INNER JOIN " + "\r\n";
            queryNew = queryNew + "                     ListMarketingPaymentTerm ON MarketingIncentiveDetail.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID " + "\r\n";
            queryNew = queryNew + "     WHERE           (@MarketingProgramID = -1 OR @MarketingProgramID = 0 OR MarketingIncentiveDetail.MarketingProgramID = @MarketingProgramID) AND (@MarketingIncentiveID = -1 OR @MarketingIncentiveID = 0 OR MarketingIncentiveDetail.MarketingIncentiveID = @MarketingIncentiveID) AND (@MarketingPaymentTermID = -1 OR @MarketingPaymentTermID = 0 OR MarketingIncentiveDetail.MarketingPaymentTermID = @MarketingPaymentTermID) AND (@CustomerID = 0 OR MarketingIncentiveDetail.CustomerID = @CustomerID) AND ROUND(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount, 0) > 0 " + "\r\n";//AND MarketingIncentiveMaster.Approved = 1 

            queryString = " @MarketingPaymentID Int, @MarketingProgramID Int, @MarketingIncentiveID Int, @MarketingPaymentTermID Int, @CustomerID Int, @ReadonlyMode bit " + "\r\n"; //@MarketingPaymentTermID Int, 
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@MarketingPaymentID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY MarketingIncentiveMaster.RequestedDate, MarketingIncentiveDetail.MarketingIncentiveID, CustomerCode, MarketingIncentiveSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY MarketingIncentiveMaster.RequestedDate, MarketingIncentiveDetail.MarketingIncentiveID, CustomerCode, MarketingIncentiveSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY MarketingIncentiveMaster.RequestedDate, MarketingIncentiveID, CustomerCode, MarketingIncentiveSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingPaymentGetDetail", queryString);



            queryString = " @MarketingPaymentID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          MarketingIncentiveDetail " + "\r\n";
            queryString = queryString + "       SET             MarketingIncentiveDetail.PaidAmount = ROUND(MarketingIncentiveDetail.PaidAmount + MarketingPaymentDetail.PaymentAmount * @UpdateOption, 0) " + "\r\n";
            queryString = queryString + "       FROM            MarketingPaymentDetail INNER JOIN " + "\r\n";
            queryString = queryString + "                       MarketingIncentiveDetail ON MarketingPaymentDetail.MarketingPaymentID = @MarketingPaymentID AND MarketingPaymentDetail.MarketingIncentiveID = MarketingIncentiveDetail.MarketingIncentiveID AND MarketingPaymentDetail.MarketingIncentiveSerialID = MarketingIncentiveDetail.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingPaymentSaveUpdate", queryString);



            SQLDatabase.CreateView("VWMarketingPaymentListingDetail", this.QueryStringMarketingPaymentListingDetail(false));
            SQLDatabase.CreateStoredProcedure("MarketingPaymentListingDetail", " @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n" + " WITH ENCRYPTION  " + "\r\n" + " AS  " + "\r\n" + this.QueryStringMarketingPaymentListingDetail(true));



            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[2];
            queryArray[0] = " SELECT TOP 1  MarketingPaymentMaster.MarketingPaymentID FROM MarketingPaymentMaster INNER JOIN MarketingIncentiveMaster ON MarketingPaymentMaster.MarketingIncentiveID = MarketingIncentiveMaster.MarketingIncentiveID AND MarketingPaymentMaster.PaymentDate < MarketingIncentiveMaster.RequestedDate ";
            queryArray[1] = " SELECT TOP 1  MarketingIncentiveID FROM MarketingIncentiveDetail WHERE (ROUND(IncentiveAmount - PaidAmount, " + GlobalVariables.Round0Amount.ToString() + ") < 0) ";

            SQLDatabase.CreateProcedureToCheckExisting("MarketingPaymentSaveConflict", queryArray);






            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 MarketingPaymentID FROM MarketingPaymentMaster WHERE MarketingPaymentID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("SPMarketingPaymentApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[3];
            queryArray[0] = " SELECT TOP 1 MarketingPaymentID FROM ABC WHERE MarketingPaymentID = @FindIdentityID ";
            queryArray[1] = " SELECT TOP 1 MarketingPaymentID FROM NMV WHERE MarketingPaymentID = @FindIdentityID ";
            queryArray[2] = " SELECT TOP 1 MarketingPaymentID FROM XYZ WHERE MarketingPaymentID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("SPMarketingPaymentEditable");// queryArray);






        }

        private string QueryStringMarketingPaymentListingDetail(bool isStoredProcedure)
        {
            string queryString = "";

            queryString = "                     SELECT          MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode, MarketingProgramMaster.MarketingProgramName, MarketingProgramMaster.MarketingProgramSubName, MarketingIncentiveMaster.RequestedDate, MarketingIncentiveMaster.PaymentPeriod, LEFT(DATENAME(Month, MarketingPaymentMaster.PaymentDate), 3) + ', ' + DATENAME(Year, MarketingPaymentMaster.PaymentDate) AS PaymentMonth, MarketingPaymentMaster.PaymentDate, " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentType.Description AS PaymentType, ListMarketingPaymentTerm.Description AS PaymentTerm, ListCustomerName.Description AS CustomerCode, ListCustomerName.DescriptionOfficial AS CustomerName, MarketingPaymentDetail.PaymentAmount, MarketingPaymentMaster.Remarks AS GeneralRemarks, MarketingPaymentDetail.Remarks " + "\r\n";
            queryString = queryString + "       FROM            MarketingPaymentMaster INNER JOIN " + "\r\n";
            queryString = queryString + "                       MarketingPaymentDetail ON MarketingPaymentMaster.MarketingPaymentID = MarketingPaymentDetail.MarketingPaymentID " + (isStoredProcedure ? " AND MarketingPaymentMaster.PaymentDate >= @LowerFillterDate AND MarketingPaymentMaster.PaymentDate <= @UpperFillterDate " : "") + " INNER JOIN " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster ON MarketingPaymentDetail.MarketingProgramID = MarketingProgramMaster.MarketingProgramID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListCustomerName ON MarketingPaymentDetail.CustomerID = ListCustomerName.CustomerID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentTerm ON MarketingPaymentDetail.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID INNER JOIN " + "\r\n";
            queryString = queryString + "                       MarketingIncentiveMaster ON MarketingPaymentDetail.MarketingIncentiveID = MarketingIncentiveMaster.MarketingIncentiveID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentType ON MarketingIncentiveMaster.MarketingPaymentTypeID = ListMarketingPaymentType.MarketingPaymentTypeID " + "\r\n";

            return queryString;
        }

    }

    public class MyBindingList<T> : BindingList<T>
    {
        protected override int FindCore(PropertyDescriptor property, object key)
        {

            // Specify search columns
            if (property == null) return -1;

            // Get list to search
            List<T> items = this.Items as List<T>;

            // Traverse list for value
            foreach (T item in items)
            {

                // Test column search value
                string value = (string)property.GetValue(item);

                // If value is the search value, return the 
                // index of the data item
                if ((string)key == value) return IndexOf(item);
            }
            return -1;
        }

        public T FindItem(PropertyDescriptor property, object key)
        {

            // Specify search columns
            if (property == null) return default(T);

            // Get list to search
            List<T> items = this.Items as List<T>;

            // Traverse list for value
            foreach (T item in items)
            {

                // Test column search value
                string value = (string)property.GetValue(item);

                // If value is the search value, return the 
                // index of the data item
                if ((string)key == value) return item;
            }
            return default(T);
        }
    }
}

